﻿'Open the database library for commands
Imports System.Data.OleDb

Public Class addCake
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim n As Integer
        For n = 2000 To 2013
            ddl_cakeYr.Items.Add(n.ToString())
        Next

    End Sub
    Protected Sub btn_addCake_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_addCake.Click
        Dim newFileName As String = ""

        If fu_cakeImg1.HasFile Then
            Dim myGUID = Guid.NewGuid
            newFileName = myGUID.ToString() + ".jpg"
            Dim picLocationOnServerHardDisk = Request.MapPath("img/cake") & "/" & newFileName
            fu_cakeImg1.SaveAs(picLocationOnServerHardDisk)
        End If

        If fu_cakeImg2.HasFile Then
            Dim myGUID = Guid.NewGuid
            newFileName = myGUID.ToString() + ".jpg"
            Dim picLocationOnServerHardDisk = Request.MapPath("img/cake") & "/" & newFileName
            fu_cakeImg2.SaveAs(picLocationOnServerHardDisk)
        End If

        If fu_cakeImg3.HasFile Then
            Dim myGUID = Guid.NewGuid
            newFileName = myGUID.ToString() + ".jpg"
            Dim picLocationOnServerHardDisk = Request.MapPath("img/cake") & "/" & newFileName
            fu_cakeImg3.SaveAs(picLocationOnServerHardDisk)
        End If

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("diycakeConnectionString").ConnectionString)

        Dim SqlString As String = "INSERT INTO cake (cakeTitle, cakeYr, cakeType, cakeDesc, cakeStep1, cakeStep2, cakeStep3, cakeMisc, cakeUser, cakeDifficulty, cakeDateAdded, cakeStar, cakeTag, cakeImg1, cakeImg2, cakeImg3, cakeUrl) VALUES (@cakeTitle, @cakeYr, @cakeType, @cakeDesc, @cakeStep1, @cakeStep2, @cakeStep3, @cakeMisc, @cakeUser, @cakeDifficulty, @cakeDateAdded, @cakeStar, @cakeTag, @cakeImg1, @cakeImg2, @cakeImg3, @cakeUrl)"

        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@cakeTitle", tb_cakeTitle.Text)
        cmd.Parameters.AddWithValue("@cakeYr", ddl_cakeYr.SelectedItem.ToString())
        cmd.Parameters.AddWithValue("@cakeType", rbl_cakeType.SelectedValue)
        cmd.Parameters.AddWithValue("@cakeDesc", tb_cakeDesc.Text)
        cmd.Parameters.AddWithValue("@cakeStep1", tb_cakeStep1.Text)
        cmd.Parameters.AddWithValue("@cakeStep2", tb_cakeStep2.Text)
        cmd.Parameters.AddWithValue("@cakeStep3", tb_cakeStep3.Text)
        cmd.Parameters.AddWithValue("@cakeMisc", tb_cakeMisc.Text)
        cmd.Parameters.AddWithValue("@cakeUser", User.Identity.Name)
        cmd.Parameters.AddWithValue("@cakeDifficulty", tb_cakeDifficulty.Text)
        cmd.Parameters.AddWithValue("@cakeDateAdded", DateTime.Now.ToString())
        cmd.Parameters.AddWithValue("@cakeStar", "1")
        cmd.Parameters.AddWithValue("@cakeTag", tb_tag.Text)
        If fu_cakeImg1.HasFile Then cmd.Parameters.AddWithValue("@cakeImg1", "img/cake/" & newFileName) Else cmd.Parameters.AddWithValue("@cakeImg1", "")
        If fu_cakeImg2.HasFile Then cmd.Parameters.AddWithValue("@cakeImg2", "img/cake/" & newFileName) Else cmd.Parameters.AddWithValue("@cakeImg2", "")
        If fu_cakeImg3.HasFile Then cmd.Parameters.AddWithValue("@cakeImg3", "img/cake/" & newFileName) Else cmd.Parameters.AddWithValue("@cakeImg3", "")
        cmd.Parameters.AddWithValue("@cakeUrl", tb_url.Text)

        oleDbConn.Open()
        cmd.ExecuteNonQuery()

        'Split the entered tags into an array of words
        Dim newTags = tb_tag.Text.Split(",")

        'Look at each tag, newTag is a temporary variable, newTags is the array 
        For Each newTag In newTags

            'Add the new tag to the tag table
            Dim newTagSql As String = "INSERT INTO tag (tag) VALUES (@cakeTag)"
            Dim cmd2 As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd2.CommandType = CommandType.Text
            cmd2.Parameters.AddWithValue("@cakeTag", newTag)
            cmd2.ExecuteNonQuery()

            'Show the ID of the last tag added
            Dim getLastIdCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM tag ORDER BY ID DESC", oleDbConn)
            Dim dr = getLastIdCmd.ExecuteReader()
            dr.Read()
            Dim lastIdTag = dr.GetValue(0)

            'Show the ID of the last cake added
            Dim getLastIdCakeCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM cake ORDER BY ID DESC", oleDbConn)
            Dim dr2 = getLastIdCakeCmd.ExecuteReader()
            dr2.Read()
            Dim lastIdCake = dr2.GetValue(0)

            'Associate cake with tag
            Dim SqlString2 As String = "Insert into cakeTagJunction(cake_fkid, tag_fkid) Values (@f1,@f2)"
            Dim newcmd As OleDbCommand = New OleDbCommand(SqlString2, oleDbConn)
            newcmd.CommandType = CommandType.Text
            newcmd.Parameters.AddWithValue("@f1", lastIdCake)
            newcmd.Parameters.AddWithValue("@f2", lastIdTag)
            newcmd.ExecuteNonQuery()
        Next

        'Show the ID of the last cake added
        Dim getLastIdCakeCmd2 As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM cake ORDER BY ID DESC", oleDbConn)
        Dim dr3 = getLastIdCakeCmd2.ExecuteReader()
        dr3.Read()
        Dim lastIdCake2 = dr3.GetValue(0)

        'Associate the cake with the user
        Dim newCakeSql As String = "INSERT INTO cakersCakeJunction (caker_fkid,cake_fkid) VALUES (@f1,@f2)"
        Dim cmd3 As OleDbCommand = New OleDbCommand(newCakeSql, oleDbConn)
        cmd3.CommandType = CommandType.Text
        cmd3.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd3.Parameters.AddWithValue("@f2", lastIdCake2)
        cmd3.ExecuteNonQuery()

        'Associate the cake with the user
        Dim newUserCakeSql As String = "UPDATE cakers SET userCake=@f1 WHERE userUName=@f2"
        Dim cmd4 As OleDbCommand = New OleDbCommand(newUserCakeSql, oleDbConn)
        cmd4.CommandType = CommandType.Text
        cmd4.Parameters.AddWithValue("@f1", lastIdCake2)
        cmd4.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd4.ExecuteNonQuery()

        Dim baseComment As String = "INSERT INTO cakeComments (comment, commentAuthor, cake_fkid, commentDateAdded) VALUES (@f1, @f2, @f3, @f4)"
        Dim cmd5 As OleDbCommand = New OleDbCommand(baseComment, oleDbConn)
        cmd5.CommandType = CommandType.Text
        cmd5.Parameters.AddWithValue("@f1", "0")
        cmd5.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd5.Parameters.AddWithValue("@f3", lastIdCake2)
        cmd5.Parameters.AddWithValue("@f4", DateTime.Now.ToString())
        cmd5.ExecuteNonQuery()

        'Show the ID of the last comment added
        Dim getLastComment As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM cakeComments ORDER BY ID DESC", oleDbConn)
        Dim dr4 = getLastComment.ExecuteReader()
        dr4.Read()
        Dim lastIdComment = dr4.GetValue(0)
        Dim baseReply As String = "INSERT INTO replies (reply, replyAuthor, replyTo, replyDateAdded, cake_fkid) VALUES (@f1, @f2, @f3, @f4, @f5)"
        Dim cmd6 As OleDbCommand = New OleDbCommand(baseReply, oleDbConn)
        cmd6.CommandType = CommandType.Text
        cmd6.Parameters.AddWithValue("@f1", "")
        cmd6.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd6.Parameters.AddWithValue("@f3", lastIdComment)
        cmd6.Parameters.AddWithValue("@f4", DateTime.Now.ToString())
        cmd6.Parameters.AddWithValue("@f5", lastIdCake2)
        cmd6.ExecuteNonQuery()

        'Split the entered urls into an array
        Dim newUrls = tb_url.Text.Split(",")

        'Look at each tag, newTag is a temporary variable, newTags is the array 
        For Each newUrl In newUrls

            'Add the new tag to the url table
            Dim newUrlSql As String = "INSERT INTO cakeUrl (url) VALUES (@cakeUrl)"
            Dim cmd7 As OleDbCommand = New OleDbCommand(newUrlSql, oleDbConn)
            cmd7.CommandType = CommandType.Text
            cmd7.Parameters.AddWithValue("@cakeUrl", newUrl)
            cmd7.ExecuteNonQuery()

            Dim getLastIdCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 url FROM cakeUrl ORDER BY ID DESC", oleDbConn)
            Dim dr = getLastIdCmd.ExecuteReader()
            dr.Read()
            Dim lastIdUrl = dr.GetValue(0)

            Dim getLastIdCakeCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM cake ORDER BY ID DESC", oleDbConn)
            Dim dr2 = getLastIdCakeCmd.ExecuteReader()
            dr2.Read()
            Dim lastIdCake = dr2.GetValue(0)

            Dim SqlString2 As String = "Insert into cakeCakeUrlJunction(cake_fkid, url_fkid) Values (@f1,@f2)"
            Dim newcmd As OleDbCommand = New OleDbCommand(SqlString2, oleDbConn)
            newcmd.CommandType = CommandType.Text
            newcmd.Parameters.AddWithValue("@f1", lastIdCake)
            newcmd.Parameters.AddWithValue("@f2", lastIdUrl)
            newcmd.ExecuteNonQuery()
        Next
        oleDbConn.Close()
        'Redirect the user to the confirmation page
        Response.Redirect("~/list.aspx" & "#last")
    End Sub

    Protected Sub rbl_cakeType_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles rbl_cakeType.SelectedIndexChanged

    End Sub
End Class